package tom.server;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import tom.client.stock.Service;
import tom.shared.Product;
import tom.shared.StkAdjust;
import tom.shared.Stock;

public class StkServiceImpl extends SessionService implements Service {

	private static final long serialVersionUID = -2800462233860198961L;

	@Override
	public Product selectProduct(String id) {
		String sql = "select * from product where comCode='"
				+ getSessVar("comCode") + "' and proCode='" + id + "'";
		Product record = null;
		try {
			ResultSet res = getDatabase().executeQuery(sql);
			while (res.next()) {
				record = new Product();
				record.proCode = res.getString("proCode");
				record.name = res.getString("name");
				record.descript = res.getString("descript");
				record.proGrp = res.getString("proGrp");
				record.unit = res.getString("unit");
				record.discount = res.getDouble("discount");
				record.sellPrice = res.getDouble("sellPrice");
				record.purPrice = res.getDouble("purPrice");
				record.purVAT = res.getString("purVAT");
				record.sellVAT = res.getString("sellVAT");
				record.minPrice = res.getDouble("minPrice");
				record.incomeAcc = res.getString("incomeAcc");
				record.costAcc = res.getString("costAcc");
				record.stockItem = res.getBoolean("stockItem");
				record.sellItem = res.getBoolean("sellItem");
				record.buyItem = res.getBoolean("buyItem");
				record.active = res.getBoolean("active");
				record.creUser = res.getString("creUser");
				record.creTime = res.getString("creTime");
				record.stkCode = res.getString("stkCode");
				record.stocks = this.selectStock(record.proCode);
				record.dc1 = res.getDouble("dc1");
				record.dc2 = res.getDouble("dc2");
				record.dc3 = res.getDouble("dc3");
				record.dc4 = res.getDouble("dc4");
				record.dc5 = res.getDouble("dc5");
				record.barcode = res.getString("barcode");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return record;
	}

	@Override
	public boolean deleteProduct(String id) {
		int rowEffect = 0;
		rowEffect = super.getDatabase().executeUpdate(
				"delete from product where comCode='" + getSessVar("comCode")
						+ "' and proCode='" + id + "'");
		log(super.getSessVar("user") + " delete product " + id);
		return rowEffect > 0;
	}

	@Override
	public boolean insertProduct(Product record) {
		Connection conn = super.getDatabase().getConnection();
		PreparedStatement stmt;
		int rowEffect = 0;
		try {
			stmt = conn.prepareStatement("insert into product"
					+ " values (?,?,?,?,?," + "?,?,?,?,?," + "?,?,?,?,?,"
					+ "?,?,?,?,?," + "?,?,?,?,?,now(),?,now(),?)");

			stmt.setString(1, this.getSessVar("comCode"));
			stmt.setString(2, record.proCode);
			stmt.setString(3, record.name);
			stmt.setString(4, record.proGrp);
			stmt.setString(5, record.unit);
			stmt.setDouble(6, record.discount);
			stmt.setDouble(7, record.sellPrice);
			stmt.setDouble(8, record.purPrice);
			stmt.setDouble(9, record.minPrice);
			stmt.setString(10, record.purVAT);
			stmt.setString(11, record.sellVAT);
			stmt.setString(12, record.incomeAcc);
			stmt.setString(13, record.costAcc);
			stmt.setBoolean(14, record.stockItem);
			stmt.setBoolean(15, record.sellItem);
			stmt.setBoolean(16, record.buyItem);
			stmt.setBoolean(17, record.active);
			stmt.setString(18, record.descript);
			stmt.setDouble(19, record.dc1);
			stmt.setDouble(20, record.dc2);
			stmt.setDouble(21, record.dc3);
			stmt.setDouble(22, record.dc4);
			stmt.setDouble(23, record.dc5);
			stmt.setString(24, record.stkCode);
			stmt.setString(25, this.getSessVar("user"));
			stmt.setString(26, this.getSessVar("user"));
			stmt.setString(27, record.barcode);
			rowEffect = stmt.executeUpdate();
		} catch (SQLException e) {
			log(e.getMessage());
		}
		return rowEffect > 0;
	}

	@Override
	public boolean updateProduct(Product record) {
		Connection conn = super.getDatabase().getConnection();
		PreparedStatement stmt;
		int rowEffect = 0;
		if (record != null) {
			System.out.println(record);
			try {
				stmt = conn
						.prepareStatement("update product set name=?, descript=?, progrp=?, unit=?,"
								+ "discount=?,sellPrice=?,purVAT=?,sellVAT=?,minPrice=?,"
								+ "incomeAcc=?,costAcc=?,stockItem=?,sellItem=?,buyItem=?,"
								+ "active=?, modUser=?,dc1=?,dc2=?,dc3=?,dc4=?,dc5=?,barcode=? "
								+ "where comCode=? and proCode=?");
				stmt.setString(1, record.name);
				stmt.setString(2, record.descript);
				stmt.setString(3, record.proGrp);
				stmt.setString(4, record.unit);
				stmt.setDouble(5, record.discount);
				stmt.setDouble(6, record.sellPrice);
				stmt.setString(7, record.purVAT);
				stmt.setString(8, record.sellVAT);
				stmt.setDouble(9, record.minPrice);
				stmt.setString(10, record.incomeAcc);
				stmt.setString(11, record.costAcc);
				stmt.setBoolean(12, record.stockItem);
				stmt.setBoolean(13, record.sellItem);
				stmt.setBoolean(14, record.buyItem);
				stmt.setBoolean(15, record.active);
				stmt.setString(16, this.getSessVar("user"));
				stmt.setDouble(17, record.dc1);
				stmt.setDouble(18, record.dc2);
				stmt.setDouble(19, record.dc3);
				stmt.setDouble(20, record.dc4);
				stmt.setDouble(21, record.dc5);
				stmt.setString(22, record.barcode);
				stmt.setString(23, this.getSessVar("comCode"));
				stmt.setString(24, record.proCode);
				rowEffect = stmt.executeUpdate();
			} catch (SQLException e) {
				log(e.getMessage());
			}
		}
		return rowEffect > 0;
	}

	@Override
	public List<Stock> selectStock(String proCode) {
		String sql = "select * from stock where comCode='"
				+ getSessVar("comCode") + "' and proCode='" + proCode
				+ "' order by priority desc, stkCode";
		Stock record = null;
		List<Stock> list = new ArrayList<Stock>();
		try {
			ResultSet res = getDatabase().executeQuery(sql);
			while (res.next()) {
				record = new Stock();
				record.proCode = res.getString("proCode");
				record.stkCode = res.getString("stkCode");
				record.balance = res.getInt("balance");
				record.rbalance = res.getInt("rbalance");
				record.minQty = res.getInt("minQty");
				record.orderQty = res.getInt("orderQty");
				record.priority = res.getInt("priority");
				list.add(record);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public boolean updateStock(String proCode, String invNo, String fromStock,
			String toStock, Integer qty) {
		int rowEffect = 0;
		Connection conn = super.getDatabase().getConnection();
		PreparedStatement stmt;
		if (qty != 0) {
			log(super.getSessVar("user") + " update stock " + fromStock
					+ " to " + toStock + " qty= " + qty);
			if (fromStock != null && !fromStock.equals("")) {
				rowEffect = super.getDatabase().executeUpdate(
						"update stock set balance = balance - " + qty
								+ ",rbalance = rbalance - " + qty
								+ " where comCode='" + getSessVar("comCode")
								+ "' and stkCode='" + fromStock
								+ "' and proCode='" + proCode + "'");

				if (rowEffect == 0) {
					try {
						stmt = conn
								.prepareStatement("insert into stock(comCode, stkCode, proCode, balance, rbalance, creUser, creTime)"
										+ " values (?,?,?,?,?,?,now())");
						stmt.setString(1, getSessVar("comCode"));
						stmt.setString(2, fromStock);
						stmt.setString(3, proCode);
						stmt.setInt(4, -qty);
						stmt.setInt(5, -qty);
						stmt.setString(6, getSessVar("user"));
						stmt.execute();
					} catch (SQLException e) {
						e.printStackTrace();
					}

				}
			}
			if (toStock != null && !toStock.equals("")) {
				rowEffect = super.getDatabase().executeUpdate(
						"update stock set balance = balance + " + qty
								+ ",rbalance = rbalance + " + qty
								+ " where comCode='" + getSessVar("comCode")
								+ "' and stkCode='" + toStock
								+ "' and proCode='" + proCode + "'");
				if (rowEffect == 0) {
					try {
						stmt = conn
								.prepareStatement("insert into stock(comCode, stkCode, proCode, balance, rbalance, creUser, creTime)"
										+ " values (?,?,?,?,?,?,now())");
						stmt.setString(1, getSessVar("comCode"));
						stmt.setString(2, toStock);
						stmt.setString(3, proCode);
						stmt.setInt(4, qty);
						stmt.setInt(5, qty);
						stmt.setString(6, getSessVar("user"));
						stmt.execute();
					} catch (SQLException e) {
						e.printStackTrace();
					}

				}
			}
			try {
				stmt = conn
						.prepareStatement("insert into stkadjust(comCode,proCode,invNo,fromStock,toStock,qty,creUser,creTime)"
								+ " values (?,?,?,?,?,?,?,now())");
				stmt.setString(1, getSessVar("comCode"));
				stmt.setString(2, proCode);
				if (invNo == null || invNo.equals(""))
					stmt.setNull(3, 0);
				else
					stmt.setString(3, invNo);
				stmt.setString(4, fromStock);
				stmt.setString(5, toStock);
				stmt.setInt(6, qty);
				stmt.setString(7, getSessVar("user"));
				stmt.execute();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return rowEffect > 0;
	}

	@Override
	public List<Double> selectVAT() {
		String sql = "select * from vat";
		List<Double> list = new ArrayList<Double>();
		try {
			ResultSet res = getDatabase().executeQuery(sql);
			while (res.next()) {
				list.add(res.getDouble("rate"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<StkAdjust> selectStkAdjustByInvoice(String invNo, String proCode) {
		String sql = "select * from stkadjust where comCode='"
				+ getSessVar("comCode") + "' and invNo='" + invNo
				+ "' and proCode='" + proCode + "' order by stkAdjNo desc";
		if (invNo == null || invNo.equals(""))
			sql = "select * from stkadjust where comCode='"
					+ getSessVar("comCode") + "' and proCode='" + proCode
					+ "' order by stkAdjNo desc";

		StkAdjust record = null;
		List<StkAdjust> list = new ArrayList<StkAdjust>();
		try {
			ResultSet res = getDatabase().executeQuery(sql);
			while (res.next()) {
				record = new StkAdjust();
				record.proCode = res.getString("proCode");
				record.invNo = res.getString("invNo");
				record.fromStock = res.getString("fromStock");
				record.toStock = res.getString("toStock");
				record.qty = res.getInt("qty");
				record.creUser = res.getString("creUser");
				record.creTime = res.getTimestamp("creTime");
				list.add(record);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public boolean deleteStkAdjust(String stkAdjCode) {
		int rowEffect = 0;
		Connection conn = super.getDatabase().getConnection();

		return rowEffect > 0;
	}

}
